| 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: Don't rely on 9i Skip Scan Indexing
"grjohnson" <Johnsog123_at_hotmail.com> wrote in message
news:32b8a689.0210202017.1400c57e_at_posting.google.com...
> G'day,
<SNIP preamble>
> I thought I'd do a bit of testing regarding this. > > I took a table and created a composite index. The table had only 66 > rows. Small enough to test on, and the primary key index had 6 > distinct keys. I figure this is lowish cardiantly for this table.
NB As others have said 66 rows is in fact too small for testing.
> Results are as follows.
My results with a somewhat different test (9.2 on windows 2000). (Pardon my inability to recall what columns are named or to type). My added comments prefixed with ******.
SQL> create table nl_objects
2 as select * from dba_objects;
Table created.
SQL> insert into nl_objects
2 select * from nl_objects;
27533 rows created.
SQL> / 55066 rows created.
SQL> / 110132 rows created.
SQL> commit;
Commit complete.
SQL> select count(*)
2 .
SQL> set autot traceonly explain
SQL> select count(*)
2 from nl_objects
3 where object_type='PACKAGE'
4 and object_status='VALID'
5 AND OBJECT_ID=647;
and object_status='VALID'
*
ERROR at line 4:
ORA-00904: "OBJECT_STATUS": invalid identifier
SQL> ED
Wrote file afiedt.buf
1 select count(*)
2 from nl_objects
3 where object_type='PACKAGE'
4 and status='VALID'
5* AND OBJECT_ID=647
SQL> /
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'NL_OBJECTS'
SQL> SET AUTOT ON EXPLAIN STAT
SQL> /
COUNT(*)
8
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'NL_OBJECTS'
Statistics
0 recursive calls
0 db block gets
3023 consistent gets
1853 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> CREATE INDEX I_NL_OBJECTS
2 ON NL_OBJECTS(OBJECT_TYPE,OBJECT_STATUS,OBJECT_ID);
ON NL_OBJECTS(OBJECT_TYPE,OBJECT_STATUS,OBJECT_ID)
*
SQL> ED
Wrote file afiedt.buf
1 CREATE INDEX I_NL_OBJECTS
2* ON NL_OBJECTS(OBJECT_TYPE,STATUS,OBJECT_ID)
SQL> /
Index created.
******<NB Object type will be of low cardinality lets give ourselves a chance) >
SQL> ANALYZE TABLE NL_OBJECTS COMPUTE STATISTICS; Table analyzed.
SQL> select count(*)
2 from nl_objects
3 where object_type='PACKAGE'
4 and status='VALID'
5 AND OBJECT_ID=647;
COUNT(*)
8
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=18) 1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'I_NL_OBJECTS' (NON-UNIQUE) (Cost=
3 Card=1 Bytes=18)
Statistics
29 recursive calls
0 db block gets
6 consistent gets
2 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> ED
Wrote file afiedt.buf
1 select count(*)
2 from nl_objects
3 where -- object_type='PACKAGE'
4 status='VALID'
5* AND OBJECT_ID=647
SQL> /
COUNT(*)
8
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=31 Card=1 Bytes=10) 1 0 SORT (AGGREGATE)
2 1 INDEX (SKIP SCAN) OF 'I_NL_OBJECTS' (NON-UNIQUE) (Cost=3
1 Card=4 Bytes=40)
Statistics
0 recursive calls
0 db block gets
31 consistent gets
28 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> So a skip scan can be used if the conditions are correct.
Note that if you do a very similar thing but with an index built in a more natural manner (starting with highest cardinality) you get the index range scan you might expect
SQL> drop index i_nl_objects;
Index dropped.
SQL> create index i_nl_objects
2 on nl_objects(object_id,object_type,status);
Index created.
SQL> select count(*)
2 from nl_objects
3 where -- object_type='PACKAGE'
4 status='VALID'
5 AND OBJECT_ID=647;
COUNT(*)
8
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10) 1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'I_NL_OBJECTS' (NON-UNIQUE) (Cost=
2 Card=4 Bytes=40)
Statistics
29 recursive calls
0 db block gets
6 consistent gets
2 physical reads
0 redo size
379 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> ed
Wrote file afiedt.buf
1 select count(*)
2 from nl_objects
3 where -- object_id=647
4 status='VALID'
5* AND OBJECT_type=PACKAGE'
SQL> /
ERROR:
ORA-01756: quoted string not properly terminated
SQL> ED
Wrote file afiedt.buf
1 select count(*)
2 from nl_objects
3 where -- object_id=647
4 status='VALID'
5* AND OBJECT_type='PACKAGE'
SQL> /
COUNT(*)
3888
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=14) 1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'I_NL_OBJECTS' (NON-UNIQUE) (C
ost=4 Card=3798 Bytes=53172)
Statistics
0 recursive calls
0 db block gets
955 consistent gets
936 physical reads
0 redo size
380 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Hope that expands/prompts further comments and testing by others.
-- 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 ******************************************Received on Mon Oct 21 2002 - 10:31:28 CDT
 |
 |