| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Myths
This is 8173.
AGLTRANSACT has 5.5million rows and the CLIENT column is heavily skewed hence my use of literals. CUR_AMOUNT shouldn't be that skewed since it has values of transactions in it. This as you say may make the test invalid or unrepresentative. I'm not sure about the rowid as I'm still going out to the table for a non-included column. I have redone the experiment using the status column which whilst still skewed has a better distribution of values and not picking any of the where clause columns in the select list. Perhaps your experiments are not picking the index because your distribution is so even that the FTS will be more efficient anyway. Status X in my query will filter out 94% of the table.
SQL> drop index leastsel;
Index dropped.
SQL> select status,count(*)
2 from agltransact
3 group by status;
S COUNT(*)
- ----------
4926960 B 157902 D 2635 E 10 N 11213 O 32110 X 343503
7 rows selected.
SQL> create index mostsel2 on agltransact(cur_amount,status);
Index created.
SQL> set autot on explain
SQL> select att_1_id,dc_flag
2 from agltransact
3 where status = 'X'
4 and cur_amount = 1000;
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=222 Bytes=222
0)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=2 Car
d=222 Bytes=2220)
2 1 INDEX (RANGE SCAN) OF 'MOSTSEL2' (NON-UNIQUE) (Cost=1 Ca
rd=222)
SQL> drop index mostsel2;
Index dropped.
SQL> create index leastsel2 on agltransact(status,cur_amount);
Index created.
SQL> select att_1_id,dc_flag
2 from agltransact
3 where status = 'X'
4 and cur_amount = 1000;
no rows selected
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=222 Bytes=222
0)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=2 Car
d=222 Bytes=2220)
2 1 INDEX (RANGE SCAN) OF 'LEASTSEL2' (NON-UNIQUE) (Cost=1 C
ard=222)
SQL> spool off
and I forgot the distributions for cur_amount.
SQL> select count(distinct cur_amount) from agltransact;
COUNT(DISTINCTCUR_AMOUNT)
436018
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4) 1 0 SORT (GROUP BY)
2 1 INDEX (FAST FULL SCAN) OF 'LEASTSEL2' (NON-UNIQUE) (Cost
=2 Card=5471848 Bytes=21887392)
SQL> select count(*) from agltransact;
COUNT(*)
5474333
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1) 1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'LEASTSEL2' (NON-UNIQUE) (Cost
=2 Card=5471848)
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ****************************************** "Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message news:3ceb6445$0$15145$afc38c87_at_news.optusnet.com.au... > In article <3ceb5bba$0$8514$ed9e5944_at_reading.news.pipex.net>, you said > (and I quote): > > Interesting. WHich version of Oracle is this? I tried this in V7, V8.0 > and V8.1 and it doesn't pick up the index at all in any of these, > provided there is a reasonable number of rows in the table with similar > distributions. Also, does it still pick up the index if you do NOT > select the rowid? That is kept in the index and would be enough for the > optimizer to bias itself, no? > > > > > SQL> CREATE INDEX MOSTSEL ON AGLTRANSACT(CUR_AMOUNT,CLIENT); > > > > Index created. > > > > SQL> analyze table agltransact estimate statistics; > > > > Table analyzed. > > > > SQL> set autotrace on explain statistics; > > SQL> select rowid,cur_amount,status > > 2 from agltransact > > 3 where client = 'DE' > > 4 and cur_amount = 293.75; > > > > ROWID CUR_AMOUNT S > > ------------------ ---------- - > > AAAGbaAALAAACwQAAh 293.75 > > <snip> > > AAAGbaAAOAAADf6AAh 293.75 > > > > 50 rows selected. > > > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=366 Card=369 Bytes=5 > > 166) > > > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=366 C > > ard=369 Bytes=5166) > > > > 2 1 INDEX (RANGE SCAN) OF 'MOSTSEL' (NON-UNIQUE) (Cost=3 Car > > d=369) > > > > Statistics > > ---------------------------------------------------------- > > 0 recursive calls > > 0 db block gets > > 36 consistent gets > > 0 physical reads > > 0 redo size > > 3434 bytes sent via SQL*Net to client > > 740 bytes received via SQL*Net from client > > 6 SQL*Net roundtrips to/from client > > 0 sorts (memory) > > 0 sorts (disk) > > 50 rows processed > > > > SQL> drop index mostsel; > > > > Index dropped. > > > > SQL> create index leastsel on agltransact(client,cur_amount); > > > > Index created. > > > > SQL> select rowid,cur_amount,status > > 2 from agltransact > > 3 where client = 'DE' > > 4 and cur_amount = 293.75; > > > > ROWID CUR_AMOUNT S > > ------------------ ---------- - > > AAAGbaAALAAACwQAAh 293.75 > > <snip> > > AAAGbaAAOAAADf6AAh 293.75 > > > > 50 rows selected. > > > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=369 Bytes=516 > > 6) > > > > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=2 Car > > d=369 Bytes=5166) > > > > 2 1 INDEX (RANGE SCAN) OF 'LEASTSEL' (NON-UNIQUE) (Cost=1 Ca > > rd=369) > > > > > > > > > > > > Statistics > > ---------------------------------------------------------- > > 0 recursive calls > > 0 db block gets > > 36 consistent gets > > 0 physical reads > > 0 redo size > > 3434 bytes sent via SQL*Net to client > > 740 bytes received via SQL*Net from client > > 6 SQL*Net roundtrips to/from client > > 0 sorts (memory) > > 0 sorts (disk) > > 50 rows processed > > > > -- > Cheers > Nuno Souto > nsouto_at_optushome.com.au.nospamReceived on Wed May 22 2002 - 05:46:08 CDT
 |
 |