Page 3 of 10 FirstFirst 12345 ... LastLast
Results 41 to 60 of 194

Thread: Geeks - Tip of the Day

  1. #41
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Supressing Repeating Values

    SELECT DECODE((LAG(DEPTNO) OVER(ORDER BY DEPTNO)),DEPTNO ,NULL,DEPTNO) DEPTNO , ENAME
    FROM EMP;


  2. #42
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    how to assign data of the deleted row to variables?
    we can assign data of the deleted row into variables using returning ... Into .
    Similar to select ... Into, returning ... Into will not work if multiple rows are deleted.
    Ex:-
    create or replace procedure test_proc as
    fname varchar2(30);
    lname varchar2(30);
    begin
    delete from emp
    where empno = 7369
    returning ename, job into fname, lname;
    dbms_output.put_line('name deleted = ' || fname || ' ' || lname);
    end;


  3. #43
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Geeks - Tip of the Day

    Need others to post here also.

    This is getting a typical Oracle / SQL thread.


  4. #44
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Packages

    You can write a procedure in package body without defining it in package specs.It considers the procedure as a private procedure for package and can be used only inside that package. But you can't define a procedure in the package specs without code in the package body , At the time of compilation it will throw error message.

    Last edited by krishnaindia2007; 04-02-2008 at 02:31 AM.

  5. #45
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes - 1
    Creating the right indexes can improve the search performance in database applications
    In general, integer fields make more efficient indexes than character fields and fixed-length data types are more efficient than variable types.

    What to Index
    Columns used frequently in Where clauses , Group by clauses or Order by clauses
    Columns used in joins, usually primary and foreign keys
    Tables where the average row length is high. An index can avoid table fetch in favor of an index scan

    What Not to Index
    Tables with a small number of rows
    Tables with heavy transaction-based I/O
    Columns not used in Where clause
    Columns with greater than 5 percent selectivity
    Wide columns (greater than 25 bytes in width)

    Some useful Data Dictionary Views related to indexes
    USER_INDEXES
    USER_IND_COLUMNS
    USER_IND_EXPRESSIONS

    When Indexes are not used
    Index will not be used if the column reference in the where clause is part of the function or expression.
    Ex:- upper(Ename) =’ADAM’ or where hiredate +7 =’10-JAN-89’


  6. #46
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes - II

    Query to display table name along with their indexes
    select user_tables.table_name, user_indexes.index_name
    from user_tables join user_indexes on user_indexes.table_name = user_tables.table_name
    order by user_tables.table_name,user_indexes.index_name;

    Query to display table name s, index names and column names
    select user_tables.table_name, user_indexes.index_name, user_ind_columns.column_name
    from user_tables join user_indexes on user_indexes.table_name = user_tables.table_name join user_ind_columns on user_indexes.index_name = user_ind_columns.index_name
    order by user_tables.table_name,user_indexes.index_name;

    Last edited by krishnaindia2007; 04-03-2008 at 02:46 AM.

  7. #47
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes -III

    Query To view function based index details

    select i.index_name, i.uniqueness, c.column_name, f.column_expression
    from all_ind_columns c, all_indexes i, all_IND_EXPRESSIONS f
    where i.table_owner = 'TABLE_OWNER_HERE'
    and i.table_name = 'TABLE_NAME_HERE'
    and i.index_name = c.index_name
    and i.owner = c.indeX_owner
    and c.index_owner = f.index_owner(+)
    and c.index_name = f.index_name(+)
    and c.table_owner = f.table_owner(+)
    and c.table_name = f.table_name(+)
    and c.column_position = f.column_position(+)
    order by i.index_name, c.column_position


  8. #48
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Geeks - Tip of the Day

    To rename a column in a table
    alter table tablename rename column oldcolumnname to newcolumnname


  9. #49
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes -IV
    How to monitor whether existing indexes are used by the queries or not?
    You can use the view v$object_usage to monitor index usage. The view displays statistics about index usage gathered from the database. Monitor for sufficient number of days. All indexes that have been used at least once can be displayed in this view.

    We can monitor the usage of particular index using the following syntax
    ALTER INDEX INDEX_NAME MONITORING USAGE;

    To see if the index has been used by a query or not
    SELECT INDEX_NAME, USED FROM V$OBJECT_USAGE WHERE OWNER = 'XXX’;

    To disable monitoring option
    ALTER INDEX INDEX_NAME NOMONITORING USAGE;

    To generate monitoring scripts for all indexes
    SELECT 'ALTER INDEX '||INDEX_NAME||' MONITORING USAGE;' FROM USER_INDEXES WHERE INDEX_TYPE='NORMAL';

    To turn off monitoring for all indexes
    SELECT 'ALTER INDEX '||INDEX_NAME||' NOMONITORING;' FROM USER_INDEXES WHERE INDEX_TYPE='NORMAL';

    Find unused indexes
    SELECT INDEX_NAME, TABLE_NAME, MONITORING, USED FROM V$OBJECT_USAGE WHERE MONITORING='YES' AND USED='NO';


  10. #50
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes -V
    When to rebuild indexes?

    when there are lots of dml operations on the table, the indexes can get fragmented. The height of the btree index can increase. In this situation it is required to rebuild index.
    Or else find the value of (number of deleted rows/total number of rows). If the result's value >=20%, then go ahead with the rebuild index statement.
    Use the following syntax to rebuilt the index.

    Alter index indexname rebuild;

    if the table is in use it will through error message ora-00054: resource busy and acquire with nowait specified

    you can do online rebuild using the following syntax

    alter index indexname rebuild online;


  11. #51
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes –VI

    When to disable indexes?

    The presence of many indexes on a table decreases the performance of updates, deletes, and inserts, because Oracle must also update the indexes associated with the table. So While inserting large volume of data you may temperorly disable index as follows.

    SQL> alter index indexname unusable;
    Index altered.

    An unusable index must be rebuilt, or dropped and re-created, before it can be used.


  12. #52
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes -VII
    What are different approaches used for creating indexes

    Proactive approach:- Creating indexes based on anticipation which columns will be most often used for joining , selection, groping and ordering is called proactive approach.

    Reactive approach :- Indexes are created based on optimizer feedback, query implementation plan, and system performance measurements.


  13. #53
    Junior Member
    Join Date
    Feb 2008
    Answers
    11

    Re: Geeks - Tip of the Day

    What is the difference between strcpy and memcpy?


  14. #54
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Geeks - Tip of the Day

    Query will return true if user is authenticated as SYSDBA.

    SELECT USERENV('ISDBA') FROM DUAL


  15. #55
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Geeks - Tip of the Day

    export / Import specific objects
    To export only procedures/fuction from database

    expdp scott/tiger directory=DPDATA1 dumpfile=expprocs.dmp include=PROCEDURE --(or FUNCTION)

    impdp scott/tiger directory=DPDATA1 dumpfile=expprocs.dmp sqlfile=procs.sql

    For this we need to create directory to hold procs.sql


  16. #56
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Collections –1
    A collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.

    When to use Collections?
    When you need to retrieve large volume of data from table, you declare a cursor, retrieve the values one at a time from the database, and process them sequentially. Instead of process them sequentially, using collections concepts you may retrieve a load of values all in one go, and load them into a collection type , so that you can carry out some sort of operation on the group of values as a whole.

    Collection Types
    PL/SQL offers three collection types.
    Index-by tables (Introduced in oracle 7 version)
    Nested tables -( Sets and bags in other languages)
    VARRAY (short for variable-size arrays). – (Arrays in other languages)

    Last edited by krishnaindia2007; 04-12-2008 at 02:40 AM.

  17. #57
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Collections –1I
    Index by table
    Syntax is
    Type <typename> is table of <col_name>
    Index by binary_integer

    <variable_name> <type_name>

    &#183; Index-by tables cannot be stored in the database
    &#183; We can’t perform DML operations on index by tables
    &#183; Index-by tables can have negative subscripts
    &#183; Data does not have to be stored in consecutive rows
    &#183; To extend an index-by table, you just specify larger subscripts.

    Ex:- Declare
    Type enametype is table of emp.ename&#37;type not null index by binary_integer;
    Type dnametype is table of dept.dname%type index by binary_integer;
    Enamelist enametype;
    Dnamelist dnametype;
    subscript binary_integer :=1;
    Begin
    For var in ( select ename, dname from emp , dept where emp.deptno = dept.deptno)
    Loop
    enamelist(subscript) := var.ename;
    dnamelist(subscript) := var.dname;
    dbms_output.put_line('Employee name is ' || enamelist(subscript) || 'Department name is' ||dnamelist(subscript));
    subscript := subscript + 1;
    end loop;
    end;
    /


  18. #58
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Collections –1II
    Nested Tables
    - It is a collection of rows represented as column within a main table. Column represents another table by itself.
    - Cannot be indexed
    - Nested tables data stored outside the main table (out of line storage)
    - Main table's column keeps a pointer to the nested table
    - No limitations for nesting in terms of number of rows
    - No restrictions on the number of columns having nested tables
    - Oracle stores the rows of a nested table in no particular order
    - While retrieving data into PL/Sql Variable, the rows are given consecutive subsripts starts with 1
    - Is always dense initially, but can become sparse after deletes.


  19. #59
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Collections –1V
    Varray
    - It is a set of objects each with the same data type.
    - Enable storage of multiple values for a single column per row
    - Number of multiple values are fixed by definition
    - Limited to one column per table
    - Array values are stored in the table itself (in line storage)
    - Indexing a varray column is not possible
    - Selection of array values is complex
    - DML operations require use of constructor methods
    - NULL values need to be specified explicitly
    - Is always dense; you can only remove elements from the end of a varray.


  20. #60
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Collections –V
    How to choose your collection type
    Use nested table when you need to...
    –Work within PL/SQL code only
    –Sparsely fill and manipulate the collection
    –Take advantage of negative index values
    Use nested tables when you need to...
    –Access the collection inside SQL (table functions, columns in tables)
    –Want to perform set operations
    Use varrays when you need to...
    –If you need to specify a maximum size to your collection
    –Access the collection inside SQL (table functions, columns in tables).


Page 3 of 10 FirstFirst 12345 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact