How To Search For A Particular Value In The View

Dec 27, 2009

Which is better to use of Views and Materialized views so as to have good performance ? Also I need to search for a particular value in the view. Any function in sql to implement this ?

View 3 Replies


ADVERTISEMENT

Performance Tuning :: Materialized View To Improve Search Queries?

Jan 31, 2012

In search queries generally we select 10-25 columns (more can't be displayed on the screen) from 5-10 tables

Say in case of insurance related application, the search might be on policy number, policy holder's first name, policy holder's last name, region, policy type etc.

And not to many columns we are displaying on the screen, say, 4 tables have collectively 4 * 20 = 80 columns, then we are displaying say 12-15 columns with 2-3 columns have aggregates on it.

since the search criteria (e.g. first name, last name, policy number etc.) is not known till last moment it will be a generic dynamic query

Is it possible that instead we create a Materialized view with query with only joining conditions but no filter conditions and selecting only columns to be displayed on the screen and then we will refresh the materialized view (to take care of recent business transactions) and fire refined query with filter criteria on this materialized view

Select col1,col2,col3,col4,col5
From tab1,tab2,tab3,tab4
Where tab1.col1=tab2.col1
And tab2.col2=tab3.col2
And tab2.col2=tab4.col2;

Will it improve performance of the search functionality

View 2 Replies View Related

Application Express :: Enable IR Search Field To Search In Hidden Columns

Jul 15, 2012

I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?

if not, that means I have to :

1- Add a text filed P1_PHONE
2- edit my report query to something similar to
> Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null
3- add button to refresh the report.

but the item P1_PHONE should be on the header of the Report region. is there a way to do this.
I am using theme 23
page template without sidebars
Report template : Reports Region.

how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.

View 7 Replies View Related

Server Administration :: How To Change Existing Materialized View To Normal View

Jan 17, 2013

can we change the existing materialized view to normal view? if yes how?

View 2 Replies View Related

Creating Materialized View On Remote Simple View

Dec 11, 2012

I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.

A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.

Is there another work around for this situation without having to work inside the data sources' environment?

View 5 Replies View Related

SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?

Oct 8, 2010

is it possible to create primary key on view and use this view for creating foreign key .

View 3 Replies View Related

SQL & PL/SQL :: Change View Definition When Select Is Running On That View

Apr 25, 2012

We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.

This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?

I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....

View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.

View 6 Replies View Related

SQL & PL/SQL :: Oracle View Have Dynamic View Function?

Jul 28, 2010

Are oracle view have Dynamic view function?

View 8 Replies View Related

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

May 2, 2012

I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.

EXEC DBMS_MVIEW.REFRESH('pro_mview','C');

But I am getting the below error.

*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1

I am able to fetch the data from that materialized view(pro_mview).

View 3 Replies View Related

Search For A Field

Mar 19, 2010

I'm wanting a query that will look up a named field and return all of the tables that it is held in. I've tried looking through manually but the database has hundreds of tables

View 1 Replies View Related

SQL & PL/SQL :: Search For Column Name?

Jul 5, 2010

Can we search for a column name if we have some value with us, and we are not sure which column it belongs to.

View 3 Replies View Related

Forms :: How To Set Lov Search Value

Jan 23, 2012

I am using oracle 10g 10.1.0.2.0 . I want to set lov search value means when we open lov then in search text box it show % but i want to set 1% .

View 5 Replies View Related

PL/SQL :: Search Items One By One

Oct 12, 2012

i have a text field search_part1 in which i put the item for search another text field i send_by which contains items a single item comes in list many times.i have to find the item one by one.i have make this statement for seraching items

Declare
typ varchar2(200);
L_NAME VARCHAR2(50) := :BLOCK3.SEARCH_PART1;
begin
go_block('BLOCK3');
first_record;
loop
if :BLOCK3.SEND_BY = L_NAME then
[code]....

bt the problem is that if a single items comes more than one time then it search only one time and next time it exit.

View 3 Replies View Related

Script To Search For Value In All Tables?

Dec 27, 2006

I would need a SQL script or a command for PLSQL that would search for a keyword %keyword% in all tables of a database instance and in a sepcified schema of a user.

how to do this interactively, without preparing a SQL script with all exisiting tables?

View 2 Replies View Related

Search All Columns In A Table

Jan 15, 2009

I have an application which deploys the data to Oracle database. It has more than 25 tables and many columns. It does not have any document explaining the deployment, so I am kind of doing reverse engineering here.

I need a script which will fetch the column name or at least table names which will match with either some string or number? I found few examples on net to find out number. But I am struggling to make it work for string.

I can not work on stored proc, as I do not have access to create that on server. So, any script will work.

View 3 Replies View Related

SQL & PL/SQL :: Column Search - Group By

Sep 27, 2010

I have a table emp where empid,employee name,job,salary are three columns.I need to retrieve empid,count of emp,emp name,emp salary of job = manager and total salary of all employees with grouping by job in one query.

View 13 Replies View Related

Forms :: Search And Update?

Mar 11, 2012

i hav a table in database with name TBH.in that table there are 25 to 30 columns.its a material details table.by using add material form i insert all the details of the material in the table.2 colums have primary key(material id and material code).

now iam making search and update form.on material code item i have WHEN-MOUSE-CLICK-TRIGGER and calling LOV and displaying record.

but the problem is when iam making changes in some fields its giving error ...UNABLE TO INSERT....FRM-40508:ORACLE error:unable to insert record.

when i check display error its showing

ORA-00001: unique constraint (DETA.SYS_C006356) violated..

View 2 Replies View Related

SQL & PL/SQL :: How To Do Selective Search Within A String

Nov 8, 2010

Assuming I have the following table with its data:

Quote:

| code | description |
| A | $abc + xyz = $cba - 2 |
| B | ($12345 + $12345)/3 |
| C | $poke + $abc = $abc + 456 - 789 |

How can I do a selective search on "description" column for any word that starts with the "$" sign? In the end, this is the resultset that I am expecting:

Quote:
| $ | count of $ |
| $abc | 3 |
| $cba | 1 |
| $12345 | 2 |
| $poke | 1 |

View 5 Replies View Related

SQL & PL/SQL :: Search B If Not In A - But Only Use Results From A If In Both Tables?

Nov 11, 2010

I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".

Table "A" may or may not have "new" data coming into the system.
Table "B" may or may not have "existing" data.

The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.

Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.

I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...

View 9 Replies View Related

PL/SQL :: Search Rows Between Timestamps

Apr 24, 2013

I ran this following query and somehow i feel the results are wrong.

SQL> select to_char(starttime,'dd-mm-YYYY hh24:mi:ss') from report where dateofmonth between to_timestamp_tz('22-Apr-2013 12:00:00','dd-mm-YYYY hh24:mi:ss') and to_timestamp_tz ('23-Apr-2013 14:00:00','dd-mm-YYYY hh24:mi:ss');

TO_CHAR(STARTTIME,'
-------------------
23-04-2013 22:43:59
23-04-2013 13:43:37

SQL> select to_timestamp_tz(starttime,'dd-mm-YYYY hh24:mi:ss') from report where dateofmonth between to_timestamp_tz('22-Apr-2013 12:00:00','dd-mm-YYYY hh24:mi:ss') and to_timestamp_tz ('23-Apr-2013 14:00:00','dd-mm-YYYY hh24:mi:ss');

TO_TIMESTAMP_TZ(STARTTIME,'DD-MM-YYYYHH24:MI:SS')
---------------------------------------------------------------------------
23-APR-13 10.43.59 PM -07:00
23-APR-13 01.43.37 PM -07:00

I am not sure why the 10 PM time is coming up in the result.

View 1 Replies View Related

PL/SQL :: Search For Same Character Combinations

Oct 30, 2013

We are having this problem of entering duplicated inventory parts in the system which physically refers to the same part. Need to create a select query to search through the list of inventory parts to find out existing parts with similar names. 

Requirement is to have a search on a specific character set and it should list even records with 50% of the characters they have entered. Characters can be anywhere in the part name with any case. 

For example: input of 'abcd1234' should return the parts with the following name... A nut 234 R49SE B12 A22Hub 156 65 a2 But not... abc 89998AK 47Xbox 360Mauser K98b 

View 11 Replies View Related

PL/SQL :: How To Search For One Value In Any Column Of Any Table

Aug 9, 2013

I am trying to find a value in any column of any table ? How can i do that ? A value example would be 'FEDERAL' Using Oracle 11g and SQL Developer. 

View 6 Replies View Related

SQL & PL/SQL :: Search Based On Column

Nov 18, 2012

can we make a search based on column , actually i have one requirement where the data is stored in column as comma seperated values like below

arif,123455,123456,77777
sait,123455,123456,66666
tomm,666666,123455,123456

I want to filter only 123455 and 123456 from this list, is it possible to get a range as i want to use it as report parameters , i can do it by using like operator as below

create table prd_mast ( prd_cd varchar2(12), prd_desc varchar2(3000));
insert into prd_mast values ('1','arif,123455,123456,77777');
insert into prd_mast values ('2','sait,123455,123456,66666');

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Search Spaces In Column

Mar 15, 2010

in my databse i have column name as email_id

in this column email id of cutomers are stored but in some emaild contains space

i want to find out such email id

View 3 Replies View Related

PL/SQL :: Reverse Search In String

Aug 13, 2012

I have table having a single column a, which contain the values "HP Laptop", can search it in either way , i mean to say either user input the HP Laptop or Laptop HP? is it possible via SQL query ?

View 11 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View

Apr 26, 2011

difference between view and materialized view?

View 1 Replies View Related

SQL & PL/SQL :: Difference Between View And Materialized View?

Aug 12, 2013

what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?

View 7 Replies View Related

Date Search By Last Week / Month?

Nov 2, 2011

I have a create a report for our dept with certain criteria. So far what I need is a report that shows last weeks numbers or sign ups. I can't get the date search or between to work.

What I need is one for month end and one for last 7 days.

here's what I have so far.

select ap.name

to_char(ap.opendate,'MM-DD-YY') "Open Date"

from [databasname]

where ap.opendate between databasename-7 and databasename-1

I really don't want to change the date myself I want the system to know when the 7 days or month is.

View 4 Replies View Related

SQL & PL/SQL :: How To Search For A Particular Text In String Column

Oct 18, 2012

I have one table for eg. TB_Fruits.

In that i have one column FruitsName(Varchar)

In that column i am storing string in comma separated values.

Select FruitsName from tb_fruits;
Result: orange,banana,apple

Now the issue is suppose if i try to insert any of these fruits name again then it should not allow me to insert.

Suppose now if i try to insert ('grapes,banana')
or
('apple,grapes')

the orange,banana,apple can be in any position.

How to check if any of these names already exist or not in the column fruitsname?

I cannot use like or INstr function here. because the position is not fixed not even string.

View 1 Replies View Related

SQL & PL/SQL :: Table Search Based On Column Value?

Aug 25, 2010

in one of the data base some of the column values are TE. i wanted to serch in what are the tables this TE values are present. so m running the below function

CREATE OR REPLACE FUNCTION find_in_schema(val VARCHAR2)
RETURN VARCHAR2 IS
v_old_table user_tab_columns.table_name%TYPE;
v_where VARCHAR2(4000);
v_first_col BOOLEAN := TRUE;

[code]....

but v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''' is giving me numaric or value error when i run as select find_in_schema('@TL') from dual; so how can i go ahed with the serch ?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved