PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2822030489
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASE | 1 | 46 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_PURCHASENO | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PURCHASENO"=9989)
14 rows selected.
Can someone explain to me what this means?
Does it mean that Oracle is using indexing to execute this query?
The clue is in the plan.
This means that you're uniquely scanning the index PK_PURCHASENO.
| Operation | Name | Rows |
---------------------------------------------
| INDEX UNIQUE SCAN | PK_PURCHASENO | 1 |
I'm assuming by the name that this is your primary key and judging by your query it's on the PURCHASENO column. A primary key must be unique so this isn't much of a surprise. You note that the rows column indicates you're only returning one row, which confirms this.a
The other important thing is this line.
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASE | 1 | 46 |
You've uniquely scanned the index but you're not just selecting data from the index, you also have to return data from the table as you've written select *. The ROWID is the unique address in the table that identifies your row. Accessing a (single) row by it's ROWID is the fastest possible way to return the data. Oracle finds the row you want in your primary key index and then uses the rowid to pick out the rest of the row. You notice that the bytes column has 46, this means that the length of the row is 46 bytes.
Were you to, instead, use the following query the access by ROWID would no longer be needed:
select purchaseno
from purchase
where purchaseno = 1000
This is because the column PURCHASENO is already in the index; there's no need to access the table. select * is considered "harmful" because of this, not only does it increase the amount of data you have to read from the disk and potentially the amount of data you have to send over a network but it also means that you may have to do additional operations in order to access your data. Only ever SELECT the rows you need.
Two last points, in your query the value PUCRHASENO is enclosed in quotes, despite being a number. If PURCHASENO is, in fact a character this is fine but if it's a number you're risking something here as you're implicitly converting the character to a number. Oracle explicitly recommends against implicit conversion for the following reasons:
SQL statements are easier to understand when you use explicit data type conversion functions.
Implicit data type conversion can have a negative impact on performance, especially if the data type of a column value is converted to that of a constant rather than the other way around.
Implicit conversion depends on the context in which it occurs and may not work the same way in every case. For example, implicit conversion from a datetime value to a VARCHAR2 value may return an unexpected year depending on the value of the NLS_DATE_FORMAT parameter.
Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.
Doing this may confuse the optimizer enough that you don't use an index, though this is unlikely to happen in a query as simple as yours.
Lastly, and this is personal preference, I find the name of the primary key of the PURCHASE table rather confusing. A better standard would be PK_<table name> rather than PK_<column name> a table can only have one primary key and only one object of that name can be in any one schema. However, it's possible to have two tables both with the same column name as a primary key.
The Oracle Performance Tuning Guide has a chapter on reading and understanding explain plans, which I would highly recommend reading.