sql - oracle query is not using indexes -


i brand new oracle , although have used sql server extensively have not had need delve details of database design ... indexes. have spent deal of time sitting through tutorials on indexes ... both in concept oracle specific.

in effort put understanding practice set simple table basic indexes.

create table "system"."tbl_person"     (             "person_id" number(10,0) not null enable,          "first_name" nvarchar2(120) not null enable,          "middle_name" nvarchar2(120),          "last_name" nvarchar2(120) not null enable,          "dob" date not null enable,          "is_male" nchar(1) default 't' not null enable,         constraint "tbl_person_pk" primary key ("person_id")    )

as can see person_id field contains unique rowid each record in table , auto-incrementing primary key.

(please don't hung on missing sql unless pertains issue of indexes not working. tried select relevant sql ddl , may have missed items. there ton of stuff there didn't think relevant issue tried trim out)

i have created couple of additional non-clustered indexes on table.

create index "system"."idx_last_name" on "system"."tbl_person" ("last_name")  create index "system"."idx_person_name" on "system"."tbl_person" ("first_name", "last_name")

when run "explain plan" on following sql notified pk index used expected. 

select * tbl_person person_id = 21

enter image description here

however when run query select particular last_name last_name index seems go ignored.

select * tbl_person last_name = 'stenstrom'

enter image description here

why not use idx_last_name? it's worth have same issue composite index idx_person_name.

the key question column "cardinality". have 5 rows estimated being returned table.

oracle has choice between 2 execution plans:

  1. load data page. scan 5 records on data page , choose one(s) match condition.
  2. load index , scan match. load data page , lookup matching record(s).

oracle has concluded 5 records, first approach faster. if load more data table, should see execution plan change. alternatively, if had select last_name instead of select *, oracle might choose index.


Comments

Post a Comment

Popular posts from this blog

Why can rails not find a route created by a helper? -

i created member route in rails 4: resources :line_items post 'decrement', on: :member end and gave matching method in line_items controller: def decrement @cart = current_cart @line_item = @cart.line_items.find_by_id(params[:id]) @line_item.decrement_quantity respond_to |format| if @line_item.save format.html { redirect_to shop_path, notice: 'line item updated.' } format.js {@current_item = @line_item} format.json { head :ok } else format.html { render action: "edit" } format.js {@current_item = @line_item} format.json { render json: @line_item.errors, status: :unprocessable_entity } end end end but when try make button: <%= button_to 'x', decrement_line_item_path(item) %> i error: no route matches [post] "/carts/25" what gives? your error message is: no route matches [post] "/carts/25" but expectin...
Read more

javascript - jquery or ashx not working -

i using ajax file upload control. it creating html: <form action="/pages/handlers/asyncfileuploadhandler.ashx" method="post" enctype="multipart/form-data" target="ajaxuploader-iframe-1397144545"> <input type="hidden" name="type" value="mindvoice"> <input type="file" id="imagefile" style="display: block; width: 80%; margin-right: auto; margin-left: auto;"> </form> and target is: <iframe width="0" height="0" style="display:none;" name="ajaxuploader-iframe-1397144545" id="ajaxuploader-iframe-1397144545"></iframe> the form submitted using .submit() . my ashx handler: <%@ webhandler language="c#" class="asyncfileuploadhandler" %> using system; using system.web; using nettpals.core; public class asyncfileuploadhandler : ihttphandler { public void processre...
Read more

opencv - DataType<cv::detail::deriv_type>::depth what is it used for -

i wondering why line used in lucas kanade tracker in opencv: datatype<cv::detail::deriv_type>::depth can explain me? in opencv, depth of mat refers type of data contained in mat 's data buffer. represented integer values correspond given data type. these integers commonly abstracted appropriate macro definition (e.g. uchar data represented macro cv_8u ). cv::datatype type-traits class provides method obtain corresponding integer value without having memorize macro means data type. there few cases user code needs use datatype::depth . more common datatype::type . a simple example shows 1 possible use of datatype::depth : cv::mat uchar_data = cv::mat::ones(3, 3, cv_8uc1); cv::mat float_data; uchar_data.convertto(float_data, cv::datatype<float>::depth); // ^^ equivalently replaced // cv_32f macro float_data.at<float>(0,1) += 0.5f; std::cout << float_data << std::endl;...
Read more