When optimizing a SQL, reminiscent of the index, but don't know should be set up separately (an index name corresponds to a field), should be established together (an index name corresponding to multiple fields), what is the difference between the two on the execution,
SQL below
SELECT "WW_BXB". "BXDBH,"
"WW_BXB". "ZZBH",
"WW_BXB". "WXS,"
"WW_BXB". "BXZZ",
"WW_BXB". "RID",
"WW_BXB". "GDXZ",
"CALL_DIRE_USER". "LEVEL1,"
"WW_BXB". "ZONE,"
"CALL_KH_LEVEL1". "KH_NAME",
"CALL_KH_LEVEL2". "KH_NAME",
"WW_BXB". "BGY",
"WW_BXB". "YD",
"WW_BXB". "WXLX",
"WW_BXB". "DHPZ_LX",
"WW_BXB". "DHPZ_ZT",
"WW_BXB". "DHPZ_SJ",
"WW_BXB". "DHPZ_CZZ",
"WW_BXB". "KSF_DH",
"KHXXDB". "KHMC",
"WW_BXB". "JZXH",
"WW_BXB". "PHONE",
"WW_BXB". "FAX,"
"CALL_DIRE_USER". "MOBILE",
"WW_BXB". "KSF_ZCBH",
"WW_BXB". "PEPSI_DH",
"WW_BXB". "PEPSI_INTERID",
"WW_BXB". "PEPSI_ZCBH",
"CALL_DIRE_USER". "YYB,"
"CALL_DIRE_USER". "YYS",
"CALL_DIRE_USER". "LINKMAN",
"CALL_DIRE_USER". "KH_SF",
"CALL_DIRE_USER". "KH_CITY",
"CALL_DIRE_USER". "KH_COUNTY",
"CALL_DIRE_USER". "KH_TOWN",
"CALL_DIRE_USER". "ADDRESS",
"WW_BXB". "TSWXLX",
"CALL_DIRE_USER". "LEVEL2 stores,"
"WW_BXB". "KSF_INTERID",
"WW_BXB". "TINGHSIN_SOUCE"
FROM "WW_BXB,"
"WW_ZTB,"
"CALL_DIRE_USER,"
"CALL_KH_LEVEL1,"
"CALL_KH_LEVEL2,"
"KHXXDB
"The WHERE (call_dire_user. Level1=call_kh_level1. Id_level1 (+)) and
(call_dire_user. Level2 stores=call_kh_level2. Id_level2 (+)) and
(ww_bxb. WXS=KHXXDB. KHBM (+)) and
(" WW_BXB ". "ZJKHBM"="CALL_DIRE_USER". "ID") and
(" WW_BXB ". "BXDBH"="WW_ZTB". "WXDBH")
And ((" WW_ZTB ". "PG"='0') and (" WW_BXB ". "BXR" not in (' top tianjin E stand ', 'Pepsi E stand', 'EXCEL))
AND (" WW_BXB ". "DHPZ_ZT is null OR" "WW_BXB". "DHPZ_ZT" & lt;> '1')
AND (" WW_BXB ". "GDXZ is null OR" "WW_BXB". "GDXZ" & lt;> '08')
AND (" WW_BXB ". "PDR is null OR" "WW_BXB". "PDR" like '%'));

Doubts point 1. Like the where the back of the (call_dire_user. Level1=call_kh_level1. Id_level1 (+)) and
(call_dire_user. Level2 stores=call_kh_level2. Id_level2 (+)) this sentence, I am call_dire_user this table level1 and level2 stores these two fields to establish two indexes respectively, or to the two fields to establish an index,
Doubts point 2 as ww_bxb each after the and the table has a field is limited, I am write this a few fields to knead together an index, or multiple indexes is set up respectively?
Is very confused

CodePudding user response:

1, you connect index built on driver table fields is useless, to build on the connection driver table fields, and connect the field can be built together;
2, behind the conditions on the single table is too complex, but also is null, the b-tree indexes if without any processing, whether it is a single field or field index and treatment of this condition is insufficient, in various fields to create bitmap index is a choice, but there can be no concurrent DML requirements table,

CodePudding user response:

reference 1st floor minsic78 response:

1, you connect index built on driver table fields is useless, to build on the connection driver table fields, and connect the field can be built together;
2, behind the conditions on the single table is too complex, but also is null, the b-tree indexes if without any processing, whether it is a single field or field index and treatment of this condition is insufficient, in various fields to create bitmap index is a choice, but it requires the table cannot have concurrent DML,

Actually, I think you like SQL, don't need any indexes, because this is a statistic, each joined the amount of data that completely uncontrolled, than table scan and then the HASH JOIN, this can say is not suitable for Oracle SQL to deal with, other relational databases is not, of course, the MPP or hadoop is the solution,

CodePudding user response:

refer to the second floor minsic78 response:

Quote: refer to 1st floor minsic78 response:

1, you connect index built on driver table fields is useless, to build on the connection driver table fields, and connect the field can be built together;
2, behind the conditions on the single table is too complex, but also is null, the b-tree indexes if without any processing, whether it is a single field or field index and treatment of this condition is insufficient, in various fields to create bitmap index is a choice, but it requires the table cannot have concurrent DML,

In fact, I think you like SQL, don't need any indexes, because this is a statistic, each joined the amount of data that completely uncontrolled, than table scan and then the HASH JOIN, this can say is not suitable for Oracle SQL to deal with, other relational databases is not, of course, the MPP or hadoop is the solution to

Can't help but three even

Since a statistics, so I would assume that this is a database of class in the warehouse, so the optimization can be considered the first floor, because this index can be optimized at least part of the query part of (according to different conditions, the query might involve small amount of data)

CodePudding user response:

reference minsic78 reply: 3/f

Quote: refer to the second floor minsic78 response:

Quote: refer to 1st floor minsic78 response:

1, you connect index built on driver table fields is useless, to build on the connection driver table fields, and connect the field can be built together;
2, behind the conditions on the single table is too complex, but also is null, the b-tree indexes if without any processing, whether it is a single field or field index and treatment of this condition is insufficient, in various fields to create bitmap index is a choice, but it requires the table cannot have concurrent DML,

In fact, I think you like SQL, don't need any indexes, because this is a statistic, each joined the amount of data that completely uncontrolled, than table scan and then the HASH JOIN, this can say is not suitable for Oracle SQL to deal with, other relational databases is not, of course, the MPP or hadoop is the solution to

Can't help but three even

Since a statistics, so I would assume that this is a database of class in the warehouse, so the optimization can be considered the first floor, because this index can be optimized at least part of the query (according to different conditions, the part has a comparatively small amount of data query might involve)

First of all, thanks to the great god of patience to solve but as non-professional personnel data is not particularly understand in some places, the driver table is refers to the left of the link is the main table, the bitmap index never used, if use, for the SQL should be how to write the

CodePudding user response:

refer to the second floor minsic78 response:

Quote: refer to 1st floor minsic78 response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull