Where as having clause is used to test some condition on the group rather than

Where as having clause is used to test some condition

This preview shows page 270 - 277 out of 412 pages.

Where as having clause is used to test some condition on the group rather than on individual rows. Where clause is used to restrict rows. But having clause is used to restrict groups. Restrict normal query by where Restrict group by function by having In where clause every record is filtered based on where. In having clause it is with aggregate records (group by functions). Posted 12th December 2011 by Prafull Dangore 9. DEC 12
Image of page 270
What is the difference between view and materialized view? Scenario: What is the difference between view and materialized view? Solution: View Materialized view A view has a logical existence. It does not contain data. A materialized view has a physical existence. Its not a database object. It is a database object. We cannot perform DML operation on view. We can perform DML operation on materialized view. When we do select * from view it will fetch the data from base table. When we do select * from materialized view it will fetch the data from materialized view. In view we cannot schedule to refresh. In materialized view we can schedule to refresh. We can keep aggregated data into materialized view. Materialized view can be created based
Image of page 271
Image of page 272
on multiple tables. Materialized View Materialized view is very essential for reporting. If we don t have the materializ ed view it will directly fetch the data from dimension and facts. This process is very slow sinc e it involves multiple joins. So the same report logic if we put in the materialized view. We can fetch the data directly from materialized view for reporting purpose. So that we can avoid multiple join s at report run time. It is always necessary to refresh the materialized view. Then it can simply perf orm select statement on materialized view. Posted 12th December 2011 by Prafull Dangore 10. DEC 12 SQL command to kill a session/sid Scenario: SQL command to kill a session/sid Solution: ALTER SYSTEM KILL SESSION 'sid,serial#';
Image of page 273
Query to find SID : select module, a.sid,machine, b.SQL_TEXT,piece from v$session a,v$sqltext b where status='ACTIVE' and a.SQL_ADDRESS=b.ADDRESS --and a.USERNAME='NAME' and sid=95 order by sid,piece; Query to find serial# select * from v$session where type = 'USER' and status = 'ACTIVE';--t0 get seria l no Posted 12th December 2011 by Prafull Dangore 11.
Image of page 274
DEC 12 SQL command to find execution timing of a query Like total execution time and so far time spent Scenario: SQL command to find execution timing of a query Like total execution time and so far time spent Solution: select target, sofar, totalwork, round((sofar/totalwork)*100,2) pct_done from v$session_longops where SID=95 and serial#=2020; Query to find SID : select module, a.sid,machine, b.SQL_TEXT,piece from v$session a,v$sqltext b where status='ACTIVE' and a.SQL_ADDRESS=b.ADDRESS --and a.USERNAME='NAME' and sid=95 order by sid,piece; Query to find serial#
Image of page 275
select * from v$session where type = 'USER' and status = 'ACTIVE';--t0 get seria l no Posted 12th December 2011 by Prafull Dangore 12. DEC 12 Query to find the SQL text of running procedure Scenario: How to find which query part/query of Procedure is running?
Image of page 276
Image of page 277

You've reached the end of your free preview.

Want to read all 412 pages?

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture