Before 11g virtual columns where created when you created a function based index
They were also hidden. On virtual columns statistics can be created witch is usefull but
you had to create an index for it.
As of 11g you can define virtual columns and are visible in the table definition.
SQL>create table aap as select rownum a1,
'AHH' a2
FROM DUAL CONNECT BY LEVEL <= 100000;
SQL>CREATE INDEX aap_i ON aap(a2);
SQL> insert into aap values(100001,'GIN');
1 row created.
SQL> insert into aap values(100002,'Gin');
1 row created.
SQL> insert into aap values(100003,'GiN');
1 row created.
SQL> commit;
SQL>begin
dbms_stats.gather_table_stats(ownname=>'GDA001',
tabname=> 'AAP',
cascade=> true,
estimate_percent=>null,
method_opt=> 'FOR ALL COLUMNS SIZE 1');
end;
/
SQL> SELECT * FROM AAP WHERE UPPER(a2)='GIN';
A1 A2
---------- ---
100001 GIN
100002 Gin
100003 GiN
Execution Plan
----------------------------------------------------------
Plan hash value: 3829027638
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 9000 | 54 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| AAP | 1000 | 9000 | 54 (6)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("A2")='GIN')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
216 consistent gets
0 physical reads
0 redo size
664 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
As you see the index is not used because of the UPPER function.
Let create the function based index.
SQL> create index f_aap_a2_i on aap(UPPER(a2)) compute statistics;
Index created.
A1 A2
---------- ---
100001 GIN
100002 Gin
100003 GiN
Execution Plan
----------------------------------------------------------
Plan hash value: 2746435280
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 9000 | 50 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| AAP | 1000 | 9000 | 50 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | F_AAP_A2_I | 400 | | 105 (0)| 00:00:02 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("A2")='GIN')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
664 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
As you see the index is now used.
SQL> select table_name, column_name, num_distinct,
density, virtual_column, hidden_column
from dba_tab_cols
where table_name = 'AAP';
TABLE COLUMN_NAME NUM_DISTINCT DENSITY VIR HID
----- --------------- ------------ ---------- --- ---
AAP SYS_NC00003$ 2 .5 NO NO
AAP A2 4 .25 NO NO
AAP A1 100003 9.9997E-06 NO NO
As you see there is a virtual column that is hidden.
and no statistics also
SQL> begin
dbms_stats.gather_table_stats(ownname=>'GDA001',
tabname=> 'AAP',
cascade=> true,
estimate_percent=>null,
method_opt=> 'FOR ALL HIDDEN COLUMNS SIZE 3');
end;
/
SQL> select table_name, column_name, num_distinct,
density, virtual_column, hidden_column
from dba_tab_cols
where table_name = 'AAP';
TABLE COLUMN_NAME NUM_DISTINCT DENSITY VIR HID
----- --------------- ------------ ---------- --- ---
AAP SYS_NC00003$ 2 .5 YES YES
AAP A2 4 .25 NO NO
AAP A1 100003 9.9997E-06 NO NO
The virtual statistics are now in place.
Lets run the statement again
SQL> SELECT * FROM AAP WHERE UPPER(a2)='GIN';
A1 A2
---------- ---
100001 GIN
100002 Gin
100003 GiN
Execution Plan
----------------------------------------------------------
Plan hash value: 2746435280
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| AAP | 3 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | F_AAP_A2_I | 3 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("A2")='GIN')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
664 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
Al you now see also the statiscts are now much more accurate. there are 100000 rows with AHH and only
3 with GIN. The rows are now 3 before that is was 100000 and with the index 400.
Now the above was the method before 11g.
This is how you can do it in 11g
SQL> drop index F_AAP_A2_I;
Index dropped.
SQL> alter table aap add (upper_naam as (UPPER(a2)));
Table altered.
SQL>begin
dbms_stats.gather_table_stats(ownname=>'GDA001',
tabname=> 'AAP',
cascade=> true,
estimate_percent=>null,
method_opt=> 'FOR ALL COLUMNS SIZE 1');
end;
/
SQL> select table_name, column_name, num_distinct,
density, virtual_column, hidden_column
from dba_tab_cols
where table_name = 'AAP';
TABLE COLUMN_NAME NUM_DISTINCT DENSITY VIR HID
----- --------------- ------------ ---------- --- ---
AAP UPPER_NAAM 2 .5 YES NO
AAP A2 4 .25 NO NO
AAP A1 100003 9.9997E-06 NO NO
As you see the virtual column upper_naam is not hidding more.
SQL> select table_name, column_name
from dba_tab_columns
where table_name = 'AAP';
TABLE COLUMN_NAME
----- ---------------
AAP A1
AAP A2
AAP UPPER_NAAM
SQL> SELECT * FROM AAP WHERE UPPER(a2)='GIN';
A1 A2 UPP
---------- --- ---
100001 GIN GIN
100002 Gin GIN
100003 GiN GIN
Execution Plan
----------------------------------------------------------
Plan hash value: 3829027638
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50002 | 634K| 56(9)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| AAP | 50002 | 634K| 56 (9)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("AAP"."UPPER_NAAM"='GIN')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
216 consistent gets
0 physical reads
0 redo size
744 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL> create index upper_naam_i on aap(upper(a2));
create index upper_naam_i on aap(upper(upper(a2))
*
ERROR at line 1:
ORA-54018: A virtual column exists for this expression
So you can't create the index because there is virtual column
SQL> create index upper_naam_i on aap(upper_naam);
Index created.
But we can create a normal index on the virtual column
SQL> SELECT index_name, index_type FROM user_indexes WHERE index_name='UPPER_NAAM_I';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
UPPER_NAAM_I FUNCTION-BASED NORMAL
As you see the index type is function based
SQL> SELECT index_name, column_expression
FROM user_ind_expressions
WHERE index_name = 'UPPER_NAAM_I';
INDEX_NAME COLUMN_EXP
------------------------------ ----------
UPPER_NAAM_I UPPER("A2")
SQL> select * from aap where upper_naam = 'GIN';
A1 A2 UPP
---------- --- ---
100001 GIN GIN
100002 Gin GIN
100003 GiN GIN
Execution Plan
----------------------------------------------------------
Plan hash value: 2505477614
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 39 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| AAP | 3 | 39 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | UPPER_NAAM_I | 3 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("UPPER_NAAM"='GIN')
Statistics
----------------------------------------------------------
28 recursive calls
144 db block gets
28 consistent gets
0 physical reads
0 redo size
744 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
And there is the index on the virtual column
Thats it
No comments:
Post a Comment