nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Query to find folders where content has been viewed

Marybeth Allsop

I am trying to run a query to build a list of folders (and their hierarchy) which contain content whose content have been viewed within a specified time period. I'm running the following query, but it returns the same folder multiple times and at various levels (see below). I thought the "distinct" would take care of that. FYI 769212 is the dataid for "Kick-Off Presentations".

select distinct a.dataid, a.name, sys_connect_by_path(a.name,':') from dtree a, dauditnew c where a.dataid=c.dataid and
a.dataid in (select a.parentid from dtree a, dauditnew c where c.dataid=a.dataid and c.auditstr = 'Fetch' and c.auditdate < '01-JAN-14' start with a.dataid=769212 connect by a.parentid = prior a.dataid) connect by a.parentid = prior a.dataid

RESULTS:

"DATAID" "NAME" "SYS_CONNECT_BY_PATH(A.NAME,':')"
"769212" "Kick-Off Presentations" ":Kick-Off Presentations"
"769212" "Kick-Off Presentations" ":Regional Markets Group (Puckett):Marketing (Lal):Kick-Off Presentations"
"769212" "Kick-Off Presentations" ":Marketing (Lal):Kick-Off Presentations"

Find more posts tagged with

Comments

Colin_Schmidt

That looks normal. It'll repeat the Parent Folder until all child folders are exhausted.

Are those listed on the right, child folders?

If you're starting with the one on the left, it'll always show. If you only want it to show on the 1st line, that takes smarter people than I, me, or myself.

Colin J.

On Fri, May 16, 2014 at 3:28 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Query to find folders where content has been viewed Posted bymarybeth.allsop@centurylink.com (Allsop, Marybeth)On 05/16/2014 03:28 PM

I am trying to run a query to build a list of folders (and their hierarchy) which contain content whose content have been viewed within a specified time period. I'm running the following query, but it returns the same folder multiple times and at various levels (see below). I thought the "distinct" would take care of that. FYI 769212 is the dataid for "Kick-Off Presentations". select distinct a.dataid, a.name, sys_connect_by_path(a.name,':') from dtree a, dauditnew c where a.dataid=c.dataid and
a.dataid in (select a.parentid from dtree a, dauditnew c where c.dataid=a.dataid and c.auditstr = 'Fetch' and c.auditdate < '01-JAN-14' start with a.dataid=769212 connect by a.parentid = prior a.dataid) connect by a.parentid = prior a.dataid RESULTS: "DATAID" "NAME" "SYS_CONNECT_BY_PATH(A.NAME,':')"
"769212" "Kick-Off Presentations" ":Kick-Off Presentations"
"769212" "Kick-Off Presentations" ":Regional Markets Group (Puckett):Marketing (Lal):Kick-Off Presentations"
"769212" "Kick-Off Presentations" ":Marketing (Lal):Kick-Off Presentations"
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Content Server

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Marybeth Allsop

Can you see a way to optimize the query? When I run it in my production system, it never completes.

Colin_Schmidt

One way that would reduce it a small amount, though, is to find the 1st dauditnew.eventid of each month and use that instead of the Date. The Auditdate is indexed, but I wonder if a "NUMBER" might be evaluated faster. Just a guess on my part. I'm no Oracle expert. I do know that using functions within Oracle increase the time quite a bit, but you are only using one. Instead of: c.auditdate < '01-JAN-14', it would be: c.eventid < '12311234'

The other alternative to using "sys_connect_by_path" would be through the DTREEANCESTORS table. The query would look a bit different pointing the "ANCESTORID = 769212" and comparing the DATAID to the Dauditnew.Dataid with auditstr = 'FETCH' as presently, but you wouldn't get the path....a requirement of yours.

You could also run the Explain Plan on the query through SQL Developer to help you look over what it has to do.

Colin J.

On Mon, May 19, 2014 at 12:02 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Query to find folders where content has been viewed Posted bymarybeth.allsop@centurylink.com (Allsop, Marybeth)On 05/19/2014 12:00 PM

Can you see a way to optimize the query? When I run it in my production system, it never completes.
[To post a comment, use the normal reply function]
Topic: Query to find folders where content has been viewed
Forum: Content Server LiveReports Forum
Content Server: Content Server

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Marybeth Allsop

Thank-you. I'll give that a try.

Lindsay_Davies

Hi Marybeth,

I think you have made your query particularly challenging for the database to handle.

I hope you have not run this as a LiveReport as it will lock a thread until it completes – which could take days!

You are using double connect by prior constructs.

Also the table aliases in the outer query are the same as the aliases in the nested select.

Then there are several things that make it generate massive row counts – Cartesian joins.

Your sample output seems to come from a different query as it looks like it lists the path reading up not down the hierarchy.

The name value is on the right of the path string.

First build the dataid list of the hierarchy using connect by prior and while doing that construct the path.

Then filter those rows based on what has a matching audit.

You might think the first part will build too large a list but… most content probably does get fetched and there are typically massively more audit rows than dtree rows.

Try this…

select distinct a.dataid, a.name, a.path

from (select dataid, name, sys_connect_by_path(name,':') path from dtree start with parentid= 769212 connect by parentid = prior dataid ) a, dauditnew da

where a.dataid=da.dataid and auditstr='Fetch' and auditdate<'01-JAN-14'

You will get any parent folder name embedded in the path for items in sub-folders, but I guess it is the item with the viewed content that you are looking for.

After all, you are starting with a given folder and working down a hierarchy, so you already know where you are starting, with respect to a full path.

I assume you want to know the objects that are fetched, not just folder that had any contained object fetched.

If this isn’t what you are looking for, then describe in words the data you want to see and where it comes from.

Regards

Lindsay

Lindsay Davies

European Escalation Team

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2014 May 19, Mon 17:03
To: eLink Recipient
Subject: Query to find folders where content has been viewed

Query to find folders where content has been viewed

Posted bymarybeth.allsop@centurylink.com (Allsop, Marybeth) On 05/19/2014 12:00 PM

Can you see a way to optimize the query? When I run it in my production system, it never completes.

[To post a comment, use the normal reply function]
Topic:	Query to find folders where content has been viewed
Forum:	Content Server LiveReports Forum
Content Server:	Content Server

Marybeth Allsop

Wow, did this ever speed things up. Thank-you so much! I was initially asked to find "any folder that has not been touched since xxxx," but I can live with this. Thanks again.