Stack Overflow
Stack Overflow

Questions tagged [sql-execution-plan]

A execution, or query plan, is the breakdown of steps the database engine uses to get a result.

A execution, or query plan, is the breakdown of steps the database engine uses to get a result. Specifically it is the plan that the optimizer determines is the best, most efficient, way to get the data.

The execution plan can be analyzed to determine what is happening and how to improve efficiency. Including finding if an index is being scanned or missing an index altogether. There is also many points of analytic data that can be acquired via the plan including things such as estimated and actual number of rows.

1429 questions
15
votes
3 answers

Why does this sql query do a key lookup?

I have a table User with a bunch of indexes. One of them is a unique index on the AccountIdentifier column. Since this is a unique index, why is a key lookup required in addition to the index seek? The index seek tooltip reports that only one record…
BC.
  • 24,298
  • 12
  • 47
  • 62
15
votes
7 answers

Very slow DELETE query

I have problems with SQL performance. For sudden reason the following queries are very slow: I have two lists which contains Id's of a certain table. I need to delete all records from the first list if the Id's already exists in the second…
hwcverwe
  • 5,287
  • 7
  • 35
  • 63
14
votes
3 answers

disable explain in Rails 3.2

Is it possible to disable the new the explain functionality in Rails 3.2 globally via configuration? I'm using activerecord-sqlserver-adapter 3.2.1 and there appear to be some bugs with the explain (show plan) portion of the gem.
Patrick Klingemann
  • 8,884
  • 4
  • 44
  • 51
14
votes
1 answer

What does loop in explain analyze statement mean?

I am profiling my query. postgres=# explain analyze select * from student; QUERY PLAN ------------------------------------------------------------------------------------------------------ Seq Scan on…
Mangu Singh Rajpurohit
  • 10,806
  • 4
  • 68
  • 97
14
votes
2 answers

Use Hints for views?

I have a view and I want to query my view like that to hint some index from a base table,can I do that? I mean: --view create or replace view temp_view as select col1,col2,col3 from table1,table2.... I have an index on table1.col1 called…
kupa
  • 1,861
  • 5
  • 25
  • 41
14
votes
1 answer

Difference between table access by index rowid BATCHED and table access by index rowid

I'm using EXPLAIN PLAN in an Oracle database on a simple SELECT statement just to find out how it works. In one output of EXPLAIN PLAN there is mentioned table access by index rowid and in the other there is table access by index rowid BATCHED. What…
T.Poe
  • 1,949
  • 6
  • 28
  • 59
13
votes
2 answers

Large amount of projected I/O with Oracle, even if only a single record is fetched

I often encounter the following situation in my Oracle execution plans: Operation | Object | Order | Rows | Bytes | Projection ----------------------------+---------+-------+------+-------+------------- TABLE ACCESS BY INDEX ROWID…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
13
votes
4 answers

Oracle Execution Plan

I am using Oracle 11g and Toad for Oracle. How can I display execution plan for queries? In Sql server management studio execution plan can be displayed as graphical format. Is there any functionality/tool like that on Toad for oracle?
Umut Derbentoğlu
  • 1,146
  • 5
  • 18
  • 39
13
votes
2 answers

Non-negligible execution plan difference with Oracle when using jdbc Timestamp or Date

I'm analysing Oracle execution plans and found an astonishing fact. Check out this query. The hint is just to display that I have an index and I'd expect Oracle to use it for range scans: // execute_at is of type DATE. PreparedStatement stmt =…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
13
votes
1 answer

Postgresql - How to speed up for updating huge table(100 million rows)?

I have two huge tables: Table "public.tx_input1_new" (100,000,000 rows) Column | Type | Modifiers ----------------|-----------------------------|---------- blk_hash | character varying(500) | blk_time …
user3383856
  • 131
  • 1
  • 1
  • 6
13
votes
1 answer

Any guides on learning how to interpret a SQL query's Execution Plan in SQL Server 2005?

Is there any good article, tutorial or similar references on interpreting the Execution Plan of a query in SQL Server 2005?
alextansc
  • 4,626
  • 6
  • 29
  • 45
12
votes
2 answers

Do foreign key constraints influence query transformations in Oracle?

I have a situation like this: create table a( a_id number(38) not null, constraint pk_a primary key (id) ); create table b( a_id number(38) not null ); create index b_a_id_index on b(a_id); Now b.a_id is in fact meant to be a foreign key…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
12
votes
1 answer

Tools for visualising execution xml plans as HTML

Are there any tools / XSLT style sheets around for transforming the XML execution plans returned by Microsoft Visual Studio into HTML? Failing that, does anyone know of any techniques that can be used to display charts in HTML suitable for…
Justin
  • 84,773
  • 49
  • 224
  • 367
12
votes
2 answers

JDBC Oracle - Fetch explain plan for query

Im wondering how I can fetch the explain plan using Java. Reason I need this is because we have a framework where special users can craft reports. These reports sometimes build huge queries in which we want to explain on the fly and store the cost…
Chris Dale
  • 2,222
  • 2
  • 26
  • 39
12
votes
3 answers

MySQL, delete and index hint

I have to delete about 10K rows from a table that has more than 100 million rows based on some criteria. When I execute the query, it takes about 5 minutes. I ran an explain plan (the delete query converted to select * since MySQL does not support…
Manuel Darveau
  • 4,585
  • 5
  • 26
  • 36
1 2 3
…
95 96