Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    singulars
    1. This table or related slice is empty.
    1. POIssue with Oracle bind variables not using index properly
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USbpgergo
    1. USbpgergo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThat will *require* a table-scan, because it's providing a value for non-indexed columns `purchase_name` and `purchase_price`—i.e, there is *no possible way* oracle could run that query without a table scan. However, for the query I provided, it is easy to see that `NULL IS NULL` should always evaluate to true, and thus these `WHERE` clauses can be skipped (and indeed, Oracle does just that for the non-bind-variable query). And I don't think "The problem is lack of indexes"—I really don't want to have to index every column, I just expect some inputs to allow it to run faster than others.
      singulars
      1. PO
      1. USJay Sullivan
    2. COOK, basically we're on the same platform. The problem is, as far as I know, the plan is generated for parametrized queries _before_ the parameters are evaluated. That makes it possible to reuse the plan, thus save the effort to generate it again when you run the same query with different parameters. How would the Oracle find out if the index on purchase_id field can be used or full table scan is needed from the parametrized query?
      singulars
      1. PO
      1. USbpgergo
    3. CO@bpgergo - this is improved in 11gR2. What you've shown won't require a full table scan, having non-indexed columns in the filter doesn't preclude the `purchase_id` index being used. Since that is the only index in play, I'd expect this to perform exactly the same as the `null` version (third query in the question). The plan will just have different filters, it won't affect access.
      singulars
      1. PO
      1. USAlex Poole
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload