Oracle 10g SQL分頁查詢語句和效率分析

Oracle 10g SQL分頁查詢語句和效率分析

在使用Oracle資料庫進行分頁查詢演算法設計時，分頁查詢的SQL語句基本上可以按照下面給出的模板來進行套用：
 
分頁查詢格式：
 SELECT * FROM
(
 SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 20
 )
 WHERE RN >= 11

其中最內層的查詢SELECT * FROM TABLE_NAME表示不進行翻頁的原始查詢語句。ROWNUM <= 20和RN >= 11控制分頁查詢的每頁的範圍。
 上面給出的這個分頁查詢語句，在大多數情況擁有較高的效率。分頁的目的就是控制輸出結果集大小，將結果儘快的返回。在上面的分頁查詢語句中，這種考慮主要體現在WHERE ROWNUM <= 20這句上。
 選擇第11到20條記錄存在兩種方法，一種是上面例子中展示的在查詢的第二層通過ROWNUM <= 20來控制最大值，在查詢的最外層控制最小值。而另一種方式是去掉查詢第二層的WHERE ROWNUM <= 20語句，在查詢的最外層控制分頁的最小值和最大值。這是，查詢語句如下：
 SELECT * FROM
(
 SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
 WHERE RN BETWEEN 11 AND 20
 
對比這兩種寫法，絕大多數的情況下，第一個查詢的效率比第二個高得多。
 
這是由於CBO最佳化模式下，Oracle可以將外層的查詢條件推到內層查詢中，以提高內層查詢的執行效率。對於第一個查詢語句，第二層的查詢條件
 
WHERE ROWNUM <= 20就可以被Oracle推入到內層查詢中，這樣Oracle查詢的結果一旦超過了ROWNUM限制條件，就終止查詢將結果返回了。
 
而第二個查詢語句，由於查詢條件BETWEEN 11 AND 20是存在於查詢的第三層，而Oracle無法將第三層的查詢條件推到最內層（即使推到最內層也沒有意義，因為最內層查詢不知道RN代表什麼）。因此，對於第二個查詢語句，Oracle最內層返回給中介層的是所有滿足條件的資料，而中介層返回給最外層的也是所有資料。資料的過濾在最外層完成，顯然這個效率要比第一個查詢低得多。
 
上面分析的查詢不僅僅是針對單表的簡單查詢，對於最內層查詢是複雜的多表聯集查詢或最內層查詢包含排序的情況一樣有效。
 

Linux-6-64下安裝Oracle 12C筆記

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2

下面簡單討論一下多表聯合的情況。對於最常見的等值表串連查詢，
 
CBO一般可能會採用兩種串連方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不會考慮）。在這裡，由於使用了分頁
 
，因此指定了一個返回的最大記錄數，NESTED LOOP在返回記錄數超過最大值時可以馬上停止並將結果返回給中介層，而HASH JOIN必須處理完所有結果集（MERGE JOIN也是）。那麼在大部分的情況下，對於分頁查詢選擇NESTED LOOP作為查詢的串連方法具有較高的效率（分頁查詢的時候絕大部分的情況是查詢前幾頁的資料，越靠後面的頁數訪問幾率越小）。
 因此，如果不介意在系統中使用HINT的話，可以將分頁的查詢語句改寫為：
 
SELECT /*+ FIRST_ROWS */ * FROM
(
 SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 20
 )
 WHERE RN >= 11
 

 

下面用幾個例子來說明分頁查詢的效率。首先選擇一個比較大的表作為測試表：
 可以採用Oracle 10g R2資料庫內建的樣本方案中的OE使用者的資料表 customers表做測試：
 
下面是CUSTOMERS表的DDL定義語句，主要是為了讓大家理解表結構
 CREATE TABLE "SH"."CUSTOMERS"
  ( "CUST_ID" NUMBER NOT NULL ENABLE,
 "CUST_FIRST_NAME" VARCHAR2(20) NOT NULL ENABLE,
 "CUST_LAST_NAME" VARCHAR2(40) NOT NULL ENABLE,
 "CUST_GENDER" CHAR(1) NOT NULL ENABLE,
 "CUST_YEAR_OF_BIRTH" NUMBER(4,0) NOT NULL ENABLE,
 "CUST_MARITAL_STATUS" VARCHAR2(20),
 "CUST_STREET_ADDRESS" VARCHAR2(40) NOT NULL ENABLE,
 "CUST_POSTAL_CODE" VARCHAR2(10) NOT NULL ENABLE,
 "CUST_CITY" VARCHAR2(30) NOT NULL ENABLE,
 "CUST_CITY_ID" NUMBER NOT NULL ENABLE,
 "CUST_STATE_PROVINCE" VARCHAR2(40) NOT NULL ENABLE,
 "CUST_STATE_PROVINCE_ID" NUMBER NOT NULL ENABLE,
 "COUNTRY_ID" NUMBER NOT NULL ENABLE,
 "CUST_MAIN_PHONE_NUMBER" VARCHAR2(25) NOT NULL ENABLE,
 "CUST_INCOME_LEVEL" VARCHAR2(30),
 "CUST_CREDIT_LIMIT" NUMBER,
 "CUST_EMAIL" VARCHAR2(30),
 "CUST_TOTAL" VARCHAR2(14) NOT NULL ENABLE,
 "CUST_TOTAL_ID" NUMBER NOT NULL ENABLE,
 "CUST_SRC_ID" NUMBER,
 "CUST_EFF_FROM" DATE,
 "CUST_EFF_TO" DATE,
 "CUST_VALID" VARCHAR2(1),
  CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUST_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"  ENABLE NOVALIDATE,
  CONSTRAINT "CUSTOMERS_COUNTRY_FK" FOREIGN KEY ("COUNTRY_ID")
    REFERENCES "SH"."COUNTRIES" ("COUNTRY_ID") ENABLE NOVALIDATE
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE" ;
 
  CREATE BITMAP INDEX "SH"."CUSTOMERS_GENDER_BIX" ON "SH"."CUSTOMERS" ("CUST_GENDER")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE" ;
 
  CREATE BITMAP INDEX "SH"."CUSTOMERS_MARITAL_BIX" ON "SH"."CUSTOMERS" ("CUST_MARITAL_STATUS")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE" ;
 
  CREATE UNIQUE INDEX "SH"."CUSTOMERS_PK" ON "SH"."CUSTOMERS" ("CUST_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE" ;
 
  CREATE BITMAP INDEX "SH"."CUSTOMERS_YOB_BIX" ON "SH"."CUSTOMERS" ("CUST_YEAR_OF_BIRTH")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE" ;
 
  ALTER TABLE "SH"."CUSTOMERS" ADD CONSTRAINT "CUSTOMERS_PK" PRIMARY KEY ("CUST_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "EXAMPLE"  ENABLE NOVALIDATE;
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_ID" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_FIRST_NAME" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_LAST_NAME" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_GENDER" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_YEAR_OF_BIRTH" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_STREET_ADDRESS" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_POSTAL_CODE" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_CITY" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_CITY_ID" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_STATE_PROVINCE" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_STATE_PROVINCE_ID" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("COUNTRY_ID" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_MAIN_PHONE_NUMBER" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_TOTAL" NOT NULL ENABLE);
 
  ALTER TABLE "SH"."CUSTOMERS" MODIFY ("CUST_TOTAL_ID" NOT NULL ENABLE);
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_ID" IS 'primary key';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_FIRST_NAME" IS 'first name of the customer';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_LAST_NAME" IS 'last name of the customer';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_GENDER" IS 'gender; low cardinality attribute';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_YEAR_OF_BIRTH" IS 'customer year of birth';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_MARITAL_STATUS" IS 'customer marital status; low cardinality attribute';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_STREET_ADDRESS" IS 'customer street address';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_POSTAL_CODE" IS 'postal code of the customer';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_CITY" IS 'city where the customer lives';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_STATE_PROVINCE" IS 'customer geography: state or province';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."COUNTRY_ID" IS 'foreign key to the countries table (snowflake)';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_MAIN_PHONE_NUMBER" IS 'customer main phone number';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_INCOME_LEVEL" IS 'customer income level';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_CREDIT_LIMIT" IS 'customer credit limit';
 
    COMMENT ON COLUMN "SH"."CUSTOMERS"."CUST_EMAIL" IS 'customer email id';
 
    COMMENT ON TABLE "SH"."CUSTOMERS"  IS 'dimension table';
 
    GRANT SELECT ON "SH"."CUSTOMERS" TO "BI";

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁