Oracle 10g SQL paging query statement and Efficiency Analysis

Oracle 10g SQL paging query statement and Efficiency Analysis

When using the Oracle database for paging query algorithm design, the paging query SQL statements can basically be applied according to the template given below:
 
Paging Query format:
SELECT * FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
Where rownum <= 20
)
Where rn> = 11

SELECT * FROM TABLE_NAME indicates the original query statement that does not flip pages. ROWNUM <= 20 and RN> = 11 control the range of each page in the paging query.
The paging query statement given above has a high efficiency in most cases. The purpose of paging is to control the size of the output result set and return the result as soon as possible. In the preceding paging query statement, this consideration is mainly reflected in the where rownum <= 20 sentence.
There are two methods to select 11th to 20 records. One is that the second layer of the query shown in the preceding example uses ROWNUM <= 20 to control the maximum value, the minimum value is controlled at the outermost layer of the query. The other method is to remove the where rownum <= 20 Statement on the second layer of the query, and control the minimum and maximum paging values at the outermost layer of the query. Here is the query statement:
SELECT * FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
)
Where rn between 11 AND 20
 
In most cases, the efficiency of the first query is much higher than that of the second query.
 
This is because in the CBO optimization mode, Oracle can push the outer query conditions to the inner query to improve the execution efficiency of the inner query. For the first query statement, the second query Condition
 
Where rownum <= 20 can be pushed to the inner layer query by Oracle. Once the Oracle query result exceeds the ROWNUM restriction, the query is terminated and the result is returned.
 
The second query statement, because the query conditions BETWEEN 11 AND 20 exist on the third layer of the query, oracle cannot push the layer-3 query conditions to the innermost layer (even pushing to the innermost layer makes no sense, because the innermost layer query does not know what RN represents ). Therefore, for the second query statement, the oldest layer of Oracle returns all the data that meets the conditions to the middle layer, and the data that the middle layer returns to the outermost layer is all the data. Data filtering is completed at the outermost layer. Obviously, this efficiency is much lower than the first query.
 
The query analyzed above is not only a simple query for a single table, but also effective for complex multi-table joint queries or sorting in the innermost query.
 

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Next we will briefly discuss the situation of Multi-table join. For the most common equi join queries,
 
CBO generally uses two Connection Methods: nested loop and hash join (merge join is less efficient than hash join, which is not considered by CBO in general ). Here, because paging is used
 
Therefore, the maximum number of returned records is specified. When the number of returned records exceeds the maximum value, the nested loop can immediately stop and return the results to the intermediate layer, however, hash join must process all result sets (so does merge join ). In most cases, it is highly efficient to select nested loop as the query connection method for paging queries (most of the time when querying by page is the data of the first few pages, the lower the page number, the lower the chance of access ).
Therefore, if you don't mind using HINT in the system, you can rewrite the paging query statement:
 
SELECT/* + FIRST_ROWS */* FROM
(
Select a. *, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME)
Where rownum <= 20
)
Where rn> = 11
 

 

The following example describes the efficiency of paging query. First, select a large table as the test table:
You can use the OE user data table MERs table in the example solution provided by the Oracle 10g R2 database for testing:
 
The following is the DDL Definition Statement of the MERs table, mainly to help you understand the table structure.
Create table "SH". "MERs"
("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". "MERs" MODIFY ("CUST_ID" not null enable );

Alter table "SH". "MERs" MODIFY ("CUST_FIRST_NAME" not null enable );

Alter table "SH". "MERs" MODIFY ("CUST_LAST_NAME" not null enable );

Alter table "SH". "MERs" MODIFY ("CUST_GENDER" not null enable );

Alter table "SH". "MERs" MODIFY ("CUST_YEAR_OF_BIRTH" not null enable );

Alter table "SH". "MERs" MODIFY ("CUST_STREET_ADDRESS" not null enable );

Alter table "SH". "CUSTOMERS" MODIFY ("CUST_POSTAL_CODE" not null enable );

Alter table "SH". "MERs" 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". "MERs" MODIFY ("CUST_STATE_PROVINCE_ID" not null enable );

Alter table "SH". "MERs" MODIFY ("COUNTRY_ID" not null enable );

Alter table "SH". "MERs" 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". "MERs". "CUST_ID" IS 'Primary key ';

Comment on column "SH". "MERs". "CUST_FIRST_NAME" IS 'first name of the customer ';

Comment on column "SH". "MERs". "CUST_LAST_NAME" IS 'last name of the customer ';

Comment on column "SH". "MERs". "CUST_GENDER" IS 'gender; low cardinality attribute ';

Comment on column "SH". "MERs". "CUST_YEAR_OF_BIRTH" IS 'customer year of birth ';

Comment on column "SH". "MERs". "CUST_MARITAL_STATUS" IS 'customer marmers status; low cardinality attribute ';

Comment on column "SH". "MERs". "CUST_STREET_ADDRESS" IS 'customer street address ';

Comment on column "SH". "MERs". "CUST_POSTAL_CODE" IS 'postal code of the customer ';

Comment on column "SH". "MERs". "CUST_CITY" IS 'city where the customer lives ';

Comment on column "SH". "MERs". "CUST_STATE_PROVINCE" IS 'customer geography: state or province ';

Comment on column "SH". "MERs". "COUNTRY_ID" IS 'foreign key to the countries table (snowflake )';

Comment on column "SH". "MERs". "CUST_MAIN_PHONE_NUMBER" IS 'customer main phone number ';

Comment on column "SH". "MERs". "CUST_INCOME_LEVEL" IS 'customer income level ';

Comment on column "SH". "MERs". "CUST_CREDIT_LIMIT" IS 'customer credit limit ';

Comment on column "SH". "MERs". "CUST_EMAIL" IS 'customer email id ';

Comment on table "SH". "MERs" IS 'dimension table ';

Grant select on "SH". "CUSTOMERS" TO "BI ";

For more details, please continue to read the highlights on the next page:

1 2 Next Page