In 2002, he saw the Skip Scan that Oracle supported indexed, that is,
Assume that there is Index (A, B)
Query SELECT * from Table Where B =? The index will be available
From the structure of the index, I have confused at the time, and I think about this.
Structure guessing of jumping index scanning
First of all, Oracle will not use multiple coupons to cover the nature of the problem, now please allow me to assume that the index (A, B, c) I guessing is the index B or C also has a "address pointer" between associated values. But Whether there is a tree structure unclear if there is no tree structure, each time you enter the different value from the A, start to find the conditional data, if there is a tree structure, and then enter one A and then quickly locate it Equipment B value may enter each A value I have to enter a time I speculate that you can gradually prove that your guess is correct by controlling the data distribution of A or B value and the requirements of the Conditional ratio. Self-assigning data distribution: ab C1 1 11 2 21 2 31 3 12 1 12 2 22 2 3 When the query b = 2 does not exist: first enter a = 1, then the order ((or 2 points, etc., because it is sorted) B = 1, b = 2, found 2, no comparison 3, because it is an orderly presence tree structure: enter A = 1, quickly positioned to 2, then find the uniform condition record and then enter a = 2 repeat The above process When query c = 3, first enter a = 1, b = 1, found that there is no entry A = 1, b = 2, and the treeless structure can be quickly positioned to 3 (can also be sequentially 2 points, because It is sorted), so repeating the steps of the above, this method has a large advantage when the data is particularly large and the data can be selectively selectable, but this kind of speculation method, if a does not have the same value, and B or C is relatively small, then this structure's jumping scan will be a disaster, and Oracle will determine the statistical analysis structure but if the above speculation is not established! Then we can consider from the perspective of algorithms
That is to say, the structure itself does not change, but can enter the scan data from multiple root, branch on the index, so that the price acquisition will be higher when the index hierarchy is more, but the change of the structure is not used.
After a few months, I made a secondary test.
InDex (a, b) When the selectivity is not selective, the advantages of Oracle make the advantages of the Jumping Scan. When the selectivity of A, the jumping scan has lost the meaning. Since there is a jump, it is said that each time When scanning, there will be an entrance to go in, scan, then go in, scan from the new entrance ... This entrance is the different value of a different value SQL> CREATE TABLE T as select * from all_Objects; Table has been created. SQL> CREATE INDEX T_INDEX1 ON T (OWNER, OBJECT_NAME); index has been created. SQL> Analyze Table T compute statistics; Table has been analyzed. SQL> SET Autotrace On SQL> SELECT DATA_OBJECT_ID, CREATED from T Where Object_name = 'DBA_EXTENTS'; DATA_OBJECT_ID CREATED -------------- ---------- 12- May -02 12-May-02 Execution Plan ---------------------------------------------------------------------------------------------------------------------------------------------- ------------------ 0 Select Statement Optimizer = Choose (COST = 35 Card = 2 Bytes = 64) 1 0 Table Access (by index rowid) of 't' (COST = 35 card = 2 bytes = 64) 2 1 index (Skip scan) of 't_index1' (non-unique) (COST = 34 CA rd = 1) statistics --------------- ------------------------------------------- 0 Recursive Calls 0 DB Block Get 26 consistent gets 0 physical reads 0 redo size 467 bytes sent via SQL * Net to client 425 bytes received via SQL * Net from client 2 SQL * Net roundtrips to / from client 0 sorts (memory) 0 sorts (disk) 2 rows processed SQL > DROP INDEX T_INDEX1; index has been discarded. SQL> CREATE INDEX T_INDEX1 ON T (Object_name, Owner); index has been created. SQL> Analyze Table T compute statistics; Table has been analyzed. SQL> Select * from t where = 'test';
Owner object_name ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------ SubObject_name object_id data_Object_id Object_type -------------------------------- ------ ------------------------------ Created last_ddl_t timestamp status tgs ------- --- ---------- ------------------------- - - - Test A 30882 30882 Table 06-12 Month -02-06-December -02 2002-12-06: 17: 35: 38 Valid NNN Test B 33237 33237 TABLE 11-February -03 11-February -03 2003-02-11: 10: 07: 49 Valid nnn ooner object_name ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------- SubObject_name object_id data_Object_id Object_type ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------l l l - l - - - -------------- ------------------------- - - - Test DBEXPERT_PLAN1 30333 30333 TABLE 02 - December -02-02- December -02 2002-12-02: 17: 26: 29 Valid NNN Test LMT 33468 33468 Table Owner Object_name ------------------- ---------------------------------------- SubObject_name object_id data_object_ ID Object_Type ------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------ ------------------ Created Last_DDL_T TimeStamp Status TGS -------------------- ------------------ ------- - - - 27 - February -03 27 - February -03 2003-02-27: 09: 22: 18 Valid NNN Test Logon_history 31284 Trigger 30-December -02 30-December -02 2002-12-30: 19: 02: 08 Valid NNN Test Manlmt Owner Object_name -------------- ------------------------------------------- Subobject_name object_id data_object_id object_type ------------------------------------------------------------------------------------------------------------------------------------------ ---- ------------------ Created last_ddl_t timestamp status tgs ------------------ --- ---------------- ------- - - - 33469 33469 TABLE 27 - February -03 27 - February -03 2003-02-27: 10: 07 :
10 Valid NNN Test Plan_Table 33482 33482 Table 03- March -03 03- March -03 2003-03-03: 10: 49: 09 Valid nn ooner object_name --------------- -------------------------------------------- SubObject_name object_id data_object_id object_type - ----------------------------------- --- ------------------ Created last_ddl_t timestamp status tgs ------------------ ---- --------------------- - - Test session_history 31287 31287 TABLE 30-22-22-22-200-22-2002-12-30: 19: 00:41 Valid NNN Test T 33485 33485 Table 03- March -03 03- March -03 2003-03-03: 17: 12: 42 Valid nnn ooner object_name ------------- --------------------------------------------- Subobject_Name Object_id Data_Object_ID Object_type ----------------------------------- ---------------------- Created last_ddl_t timestamp status tgs ---------- ---------- ----------------------- - - Test Test 33483 33483 TABLE 03- March -03 2003- March -03 2003-03-03: 10:52:08 Valid Nnn Test Test_index 33484 33484 Index Owner Object_name --------------------------------------------------- ----------- Subobject_name object_id data_Object_id Object_type --------------------------------- ------------------ ------------------ Created last_ddl_t timestamp status tgs -------- - - ---------- ------------------------- - - - 03 - March -03 03-3 Month -03 2003-03-03: 10: 52: 34 Valid NNN has selected 11 lines.
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 43 Card = 927 BYtes = 79 722) 1 0 Table Access (Full) of 'T' (COST = 43 Card = 927 BYtes = 79722) Statistics --------------------------------------------------- -------- 71 Recursive Calls 0 DB Block Gets 450 Consistent Gets 0 Physical Reads 0 redo size 2264 bytes Sent Via SQL * Net to Client 425 Bytes Received Via SQL * Net from Clom Clom Clom Client 2 SQL * Net RoundTrips TO / from client 0 sorts (memory) 0 sorts (disk) 11 rows processed thanks chao_ping example was given a SELECT OWNER, OBJECT_ID fROM YAFENG WHERE object_name = 'DBA_TABLES' 16:44:01 scott @ oRA9> / OWNER OBJECT_ID --- -------------------------------------------------- ------- ---------- Public 1813 Sys 1812 Elapsed: 00: 00: 0012 ELAPSED PLAN -------------------- -------------------------------------- 0 SELECT Statement Optimizer = Choose (COST = 11 Card = 1 bytes = 22) 1 0 Table Access (by index rot = 1 bytes = 22) 2 1 Index (Skip scan) Of 'IDX_YAFENG' (COST = 10 Card = 1) 12 Consistent Gets 16:44:02 Scott @ ORA9> SELECT DISTINCT OWNER from Yafeng; Owner -------------- -------------------------------------------- Bidder Eachpay Outln Perfstat PUBLIC SCOTT SYS SYSTEM 8 rows selected 12 consistent gets: root block -> branch block. (begin with bidder: no object_name like dBA_tables) --- one block gets -> Branch block (begin with eachpay: no object_name like dba_tables) - -one block gets -> Branch Block (Begin with Outln: No Object_name Like DBA_TABLES --ONE BLOCK GETS ->
