EƒBƒVEΰ€ƒqƒtα€λ
ƒeuBƒ’ƒtƒlƒ^ƒ†BBƒTΰ

INDEXAƒƒƒTƒlƒwƒ€AƒIA쀃nAASQL(PRIMARY KEY)

ƒVοΚƒtBBEƒkBƒVEƒ\Eƒ‹BƒVEƒPBBEƒFEι₯ƒbEλ
ƒjβΝƒjBBEXPLAN PLAN EƒEEJEƒmAƒqAθ€λ₯uEƒbEƒTEƒPEƒ€EƒPAƒzƒNBƒZƒŒ
ƒGƒgƒJƒ…BBOSBBWindows NT 4.0
      Oracle Oracle8 server


AƒEAƒzEƒŒBƒVEƒNAƒzƒjβΝƒjAƒ}BuPRIMARY KEYAqƒTƒlƒwƒ€AƒLAƒjAAAλSQLƒnƒNAƒkBuINDEXAƒƒƒTƒlƒwƒ€AƒIA쀃nAAƒZμΉη€ƒzƒNBƒZƒŒƒL벃tAƒkAƒP


TABLE "T_TEST"Aƒ}ƒCƒVƒIƒ…AƒzA考’AƒqPRIMARY KEYAƒƒƒRξΐƒ‡AƒIA쀃jAAAJAƒP

ƒnvƒPηPRIMARY KEY
   ALTER TABLE T_TEST ADD (
    CONSTRAINT T_TEST_PKEY
    PRIMARY KEY ( 
                  A_NO
                 ,A_KNO
                 ,B_NO
                 ,B_KNO
                 ,A_SURYO
                 ,B_SURYO
                )
   USING INDEX
   PCTFREE  5
   TABLESPACE IYT
   STORAGE (
            INITIAL 100M
            NEXT    20M
            PCTINCREASE  0
           )
);



PLANINGAƒqAθ€λSQLAƒzƒNBƒZƒŒ

>> CASEvƒABBƒnvƒPη₯ƒ‰Eι₯AEJEκ₯ƒ…BƒVAƒzƒPΰΜƒAqƒTƒŠƒgꀃLAƒ\SQL
 
SQL> EXPLAIN PLAN
  2  SET STATEMENT_ID = 'SIC'
  3  FOR 
  4  SELECT A_NO FROM T_TEST
  5   WHERE B_NO = 'B-001';

ExplainAƒqƒ^ƒ‡ƒNƒLAJAƒLAƒ\Bv

SQL> SELECT DECODE(ID,0,'',LPAD(' ',3*(LEVEL - 1)) || '.' || POSITION) || ' '||
  2         OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE ||
  3         ' ' || DECODE(ID,0,'Cost = ' || POSITION ) QUERY_PLAN
  4    FROM PLAN_TABLE
  5   CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = UPPER('SIC')
  6   START WITH ID = 0 AND STATEMENT_ID = UPPER('SIC');

QUERY_PLAN
------------------------------------------------------------------------------------------------
 SELECT STATEMENT    Cost =
   .1 TABLE ACCESS FULL T_TEST
 

>>BBƒL벃tBBFULLƒNBƒRχ(INDEXAƒƒƒTƒlAο€μ€ƒnAAƒNBƒRχ)AƒƒƒPƒ„Aο€μ€ƒjAAAλ

********************************************************************************************************************************************

>> CASEvƒCBBƒnvƒPη₯ƒ‰Eι₯AEJEκ₯ƒ…BƒVAƒzƒRƒkƒX逃zƒPΰΜƒAƒqƒcƒ~AƒLAƒjBuƒ`[ƒnuAAAJAAƒNBƒRqƒTƒŠƒgꀃLAƒ\SQL
 
SQL> EXPLAIN PLAN
  2  SET STATEMENT_ID = 'SIC'
  3  FOR 
  4  SELECT A_NO FROM T_TEST
  5   WHERE A_NO LIKE 'A%';

ExplainAƒqƒ^ƒ‡ƒNƒLAJAƒLAƒ\Bv

SQL> SELECT DECODE(ID,0,'',LPAD(' ',3*(LEVEL - 1)) || '.' || POSITION) || ' '||
  2         OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE ||
  3         ' ' || DECODE(ID,0,'Cost = ' || POSITION ) QUERY_PLAN
  4    FROM PLAN_TABLE
  5   CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = UPPER('SIC')
  6   START WITH ID = 0 AND STATEMENT_ID = UPPER('SIC');

QUERY_PLAN
---------------------------------------------------------------------------------------
 SELECT STATEMENT    Cost =
   .1 INDEX RANGE SCAN T_TEST_PKEY UNIQUE
 

>>BBƒL벃tBBINDEXAƒƒƒTƒlAο€μ€ƒ\

********************************************************************************************************************************************

>> CASEvƒEBBƒnvƒPη₯ƒ‰Eι₯AEJEκ₯ƒ…BƒVAƒzƒRƒkƒX逃zƒPΰΜƒAƒqƒcƒ~AƒLAƒjBuƒNεʁuAAAJAAƒNBƒRqƒTƒŠƒgꀃLAƒ\SQL
 
SQL> EXPLAIN PLAN
  2  SET STATEMENT_ID = 'SIC'
  3  FOR 
  4  SELECT A_NO FROM T_Y2019
  5   WHERE A_NO LIKE '%A';

ExplainAƒqƒ^ƒ‡ƒNƒLAJAƒLAƒ\Bv

SQL> SELECT DECODE(ID,0,'',LPAD(' ',3*(LEVEL - 1)) || '.' || POSITION) || ' '||
  2         OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE ||
  3         ' ' || DECODE(ID,0,'Cost = ' || POSITION ) QUERY_PLAN
  4    FROM PLAN_TABLE
  5   CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = UPPER('SIC')
  6   START WITH ID = 0 AND STATEMENT_ID = UPPER('SIC');

QUERY_PLAN
----------------------------------------------------------------------------------------
 SELECT STATEMENT    Cost =
   .1 TABLE ACCESS FULL T_TEST
 

>>BBƒL벃tBBFULLƒNBƒRχ(INDEXAƒƒƒTƒlAο€μ€ƒnAAƒNBƒRχ)AƒƒƒPƒ„Aο€μ€ƒjAAAλ

********************************************************************************************************************************************

>>BBCASEvƒGBBƒnvƒPη₯ƒ‰Eι₯AEJEκ₯ƒ…BƒVAƒzƒRƒkƒX逃zƒPΰΜƒAqƒGJAΰSQL
 
SQL> EXPLAIN PLAN
  2  SET STATEMENT_ID = 'SIC'
  3  FOR
  4  SELECT A_NO FROM T_TEST
  5   WHERE A_NO LIKE 'A%'
  6     AND A_KNO =  '00010'
  7     AND B_NO LIKE 'B%'
  8     AND NL_KNO = '00010'
  9     AND A_SURYO IS NOT NULL
 10     AND B_SURYO IS NOT NULL
 11  ;

ExplainAƒqƒ^ƒ‡ƒNƒLAJAƒLAƒ\Bv

SQL> SELECT DECODE(ID,0,'',LPAD(' ',3*(LEVEL - 1)) || '.' || POSITION) || ' '||
  2         OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE ||
  3         ' ' || DECODE(ID,0,'Cost = ' || POSITION ) QUERY_PLAN
  4    FROM PLAN_TABLE
  5   CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = UPPER('SIC')
  6   START WITH ID = 0 AND STATEMENT_ID = UPPER('SIC');

QUERY_PLAN
----------------------------------------------------------------------------------------
 SELECT STATEMENT    Cost =
   .1 INDEX RANGE SCAN T_TEST_PKEY UNIQUE
 

>>BBƒL벃tBBINDEXAƒƒƒTƒlAο€μ€ƒ\

********************************************************************************************************************************************

>>BB ƒLλΟƒ^BBƒnvƒPη₯ƒ‰Eι₯AEJEκ₯ƒ…BƒVAƒzƒRƒkƒX逃zƒPΰΜƒAJAJAƒnAASQLƒnƒNAƒ}BuINDEXAƒƒƒTƒlƒwƒ€AƒIA쀃nAA

>>BB ƒcƒ~ƒXθ  ƒmƒƒƒwƒ‰AƒnƒPΰΜƒAƒzINDEXAξΐƒ‡AƒPAλ


Copyright (C) 1999 System Infinity Corporation. All rights reserved.