我应该在该查询中使用索引吗？
我对索引有疑问，
为什么我在客户表的 CID 上使用索引，因为我正在查询该表并加入预订表。
架构预订表
Name Null? Type
----------------------------------------- -------- ----------------------------
BID NOT NULL NUMBER(16)
CID NUMBER(16)
FNO NUMBER(16)
OID NUMBER(16)
SEAT NUMBER(6)
SEAT_PRICE NUMBER(6)
FLIGHT_DATE DATE
客户
CID NOT NULL NUMBER(16)
FIRSTNAME NOT NULL VARCHAR2(10)
LASTNAME NOT NULL VARCHAR2(10)
STREET NOT NULL VARCHAR2(20)
TOWN NOT NULL VARCHAR2(10)
查询
SELECT bo.bid, cu.cid
FROM ass2master_booking bo, ass2master_customer cu
WHERE bo.cid = cu.cid and rownum < 135000;
我的问题是：通过引用查询向CID添加索引真的有什么区别吗？
我添加它是因为它被加入和选择。
我使用非结构化数据（没有索引）测试了这个查询，CPU 没有显着下降，但跟踪文件中的磁盘列从 1800 下降到 337。
非结构化磁盘为 1800，索引为 337。
通过索引正在快速执行对客户桌子进行全面扫描。
I have question regarding index,
I am using an index on CID on customer table why because I'm querying this table as well as joining with the booking table.
Schema Booking table
Name Null? Type
----------------------------------------- -------- ----------------------------
BID NOT NULL NUMBER(16)
CID NUMBER(16)
FNO NUMBER(16)
OID NUMBER(16)
SEAT NUMBER(6)
SEAT_PRICE NUMBER(6)
FLIGHT_DATE DATE
customer
CID NOT NULL NUMBER(16)
FIRSTNAME NOT NULL VARCHAR2(10)
LASTNAME NOT NULL VARCHAR2(10)
STREET NOT NULL VARCHAR2(20)
TOWN NOT NULL VARCHAR2(10)
Query
SELECT bo.bid, cu.cid
FROM ass2master_booking bo, ass2master_customer cu
WHERE bo.cid = cu.cid and rownum < 135000;
My question is: does it really make any difference whether I add an index to CID by referring to the query?
I have added it because its being joined and selected.
I tested this query with unstructured data (without index) and there is no significant decrease in CPU but the disk column in trace file has drop from 1800 to 337.
Unstructured disk was 1800, with index its 337.
By the index is doing a fast full scan on customer table.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果它能提高性能的话，答案是肯定的。
不要只是为了添加索引而添加索引。这很容易测试。索引查询要么更快，要么不是。请记住，索引会在插入期间减慢表的速度。因此，您必须权衡您的具体数据和性能需求。
The answer is yes if it improves the performance.
Don't add indexes just to add them. That's easy enough to test. The indexed query is either faster or it isn't. Remember, indexes slow down tables during inserts. So you have to weigh your specific data and performance needs.
1）我在您描述的
CUSTOMER表中没有看到CID列，这有点令人困惑。也许您的查询打算引用FNO列，或者可能有一个CID列您不小心从DESCRIBE命令的输出中截断了。2）表中有多少行？您正在获取任意 135,000 行，并且没有任何有意义的谓词，因此索引除了作为覆盖索引以便 Oracle 可以扫描索引而不是扫描表之外不会特别有用。但如果您需要向
SELECT列表添加其他列，这种优势就会消失。3）鉴于您发布的查询，我不清楚您为什么要加入
CUSTOMER表。BOOKING同时具有BID和CID，因此加入似乎没有做任何有益的事情。也许目的是加入CUSTOMER表，以便您可以向SELECT列表添加其他列。但是，如果是这种情况，覆盖索引的好处可能会丢失（当然，除非您也将附加列添加到索引中）1) I'm not seeing a
CIDcolumn in theCUSTOMERtable you described which is a bit confusing. Perhaps your query intended to refer to theFNOcolumn or perhaps there is aCIDcolumn that you accidentally cut off from the output of theDESCRIBEcommand.2) How many rows are in the table? You're fetching an arbitrary 135,000 rows and you don't have any meaningful predicates so an index isn't going to be particularly helpful other than as a covering index so that Oracle can scan the index rather than scanning the table. But that advantage will disappear if you need to add additional columns to the
SELECTlist.3) Given the query as you posted it, it's not obvious to my why you're joining to the
CUSTOMERtable.BOOKINGhas bothBIDandCIDso the join doesn't appear to be doing anything beneficial. Perhaps the intention was to join to theCUSTOMERtable so that you could add additional columns to theSELECTlist. If that's the case, however, the benefit of the covering index may be lost (unless, of course, you add the additional columns to the index as well)